﻿using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
//using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling;
//using Microsoft.Practices.EnterpriseLibrary.WindowsAzure.TransientFaultHandling.SqlAzure;
using Newtonsoft.Json;

namespace wamr.Worker
{
    public class CopyDatabaseTask:ManagerTask
    {
        private enum DatabaseCopyStatusEnum { COPYING, SUSPECT, ONLINE, NONE };

        //{"copy": {
        //    "overwrite":"false",
        //    "sqladminuser": "myusername",
        //    "sqladminpw": "myadminpw",
        //    "source":
        //        {"servername":"xyz", "databasename":"abc"},
        //    "target":
        //        {"servername":"123", "databasename":"234"}
        //    }
        //}
        private bool _overwriteTargetDatabase;
        private string _sqlAdminUserName;
        private string _sqlAdminPassword;
        private string _sourceServerName;
        private string _sourceDatabaseName;
        private string _targetServerName;
        private string _targetDatabaseName;
        private string _targetConnectionAlias;
        
        public CopyDatabaseTask(string ManagerMessage)
        {
            MessageIsValid = true;
            TaskType = "CopyDatabase";

            JSONClasses.JSONCopyRootObject r = JsonConvert.DeserializeObject<JSONClasses.JSONCopyRootObject>(ManagerMessage);

            if (string.IsNullOrEmpty(r.copy.overwritedatabase))
                _overwriteTargetDatabase = false;
            else
                _overwriteTargetDatabase = (r.copy.overwritedatabase == "true" ? true : false);

            // if no target specified, default it.
            if (r.copy.target == null)
                r.copy.target = new JSONClasses.JSONTarget();

            // the message must contain either:
            // sqladminuser + sqladminpw + servername + databasename OR
            // ConnectionAlias + DatabaseName
            if (string.IsNullOrEmpty(r.copy.sqladminpw) || string.IsNullOrEmpty(r.copy.sqladminuser))
            {
                if (string.IsNullOrEmpty(r.copy.target.connectionalias))
                {
                    MessageIsValid = false;
                    Trace.TraceError("Either SQL admin creds or target connection alias must be specified.");
                    return;
                }
            }
            else
            {
                _sqlAdminUserName = r.copy.sqladminuser;
                _sqlAdminPassword = r.copy.sqladminpw;
            }

            //
            // by this time, we know that either the creds are known, or the target connection string is known.
            //

            // source database name and server name must be provided
            if (string.IsNullOrEmpty(r.copy.source.databasename) || string.IsNullOrEmpty(r.copy.source.servername))
            {
                MessageIsValid = false;
                Trace.TraceError("Both the source server name and database name must be specified.");
                return;
            }

            // if target server name isn't specified, it must be the same as the source server name.
            if (string.IsNullOrEmpty(r.copy.target.servername))
                r.copy.target.servername = r.copy.source.servername;

            // if target database name isn't provided, create one based on the source database name
            if (string.IsNullOrEmpty(r.copy.target.databasename))
                r.copy.target.databasename = r.copy.source.databasename + "_copy";

            //
            // now we know that we have either creds or connection strings AND
            // we have source database and server AND
            // we have target database and server
            //

            // copy the source and target data from JSON message 
            _sourceDatabaseName = r.copy.source.databasename;
            _sourceServerName = r.copy.source.servername;

            _targetServerName = r.copy.target.servername;
            _targetDatabaseName = r.copy.target.databasename;
            if (!string.IsNullOrEmpty(r.copy.target.connectionalias))
                _targetConnectionAlias = r.copy.target.connectionalias;

            // see if this copy object is a dependency for another task
            if (r.copy.dependency != null)
                Dependency = r.copy.dependency;

            TaskDisposition = TaskDispositionEnum.NONE;
        }

        public override void ResolveDependency()
        {
            throw new NotImplementedException();
        }

        public override bool PreConditionsMet()
        {
            bool retval = true;
            TaskDisposition = TaskDispositionEnum.PROCEED;

            if (!MessageIsValid)
            {
                TaskDisposition = TaskDispositionEnum.FAIL;
                retval = false;
            }

            // Precondition: target server must exist and we must be able to access it from this IP
            bool serverExists = DoesServerExist(_targetServerName, _sqlAdminUserName, _sqlAdminPassword, _targetConnectionAlias);
            if (!serverExists)
            {
                TaskDisposition = TaskDispositionEnum.FAIL;
                Trace.TraceInformation(string.Format("Precondition for copy database task not met: either the server {0} doesn't exist or can't get access to it.", _targetServerName));
                retval = false;
            }

            // Precondition: if the user doesn't say overwrite the target, but the target exists, fail
            if (!_overwriteTargetDatabase)
            {
                bool databaseExists = DoesDatabaseExist(_targetServerName, _targetDatabaseName, _sqlAdminUserName, _sqlAdminPassword, _targetConnectionAlias);
                if (databaseExists)
                {
                    TaskDisposition = TaskDispositionEnum.FAIL;
                    Trace.TraceInformation(string.Format("Precondition for copy database task not met: target database {0}.{1} exists and Overwrite not specified.", _targetServerName, _targetDatabaseName));
                    retval = false;
                }
            }

            // Precondition: If another copy operation is already in flight, fail
            DatabaseCopyStatusEnum d = DatabaseCopyStatus(_targetServerName, _targetDatabaseName, _sqlAdminUserName, _sqlAdminPassword, _targetConnectionAlias);
            if (d == DatabaseCopyStatusEnum.COPYING || d == DatabaseCopyStatusEnum.SUSPECT)
            {
                TaskDisposition = TaskDispositionEnum.FAIL;
                Trace.TraceInformation(string.Format("Precondition for copy database task not met: another database copy operation already in flight for {1}.{2}.", _targetServerName, _targetDatabaseName));
                retval = false;
            }

            return retval;
        }

        public override bool Initiate()
        {
            if (_overwriteTargetDatabase)
            {
                bool databaseExists = DoesDatabaseExist(_targetServerName, _targetDatabaseName, _sqlAdminUserName, _sqlAdminPassword, _targetConnectionAlias); 
                if (databaseExists)
                    DropDatabase(_targetServerName, _targetDatabaseName, _sqlAdminUserName, _sqlAdminPassword, _targetConnectionAlias);
            }
            CopyDatabase(_targetServerName, _targetDatabaseName, _targetConnectionAlias, _sourceServerName, _sourceDatabaseName, _sqlAdminUserName, _sqlAdminPassword);
            return true;
        }

        public override bool Status()
        {
            DatabaseCopyStatusEnum d = DatabaseCopyStatus(_targetServerName, _targetDatabaseName, _sqlAdminUserName, _sqlAdminPassword, _targetConnectionAlias);
            if (d == DatabaseCopyStatusEnum.ONLINE)
            {
                TaskDisposition = TaskDispositionEnum.COMPLETE;
                Trace.TraceInformation(string.Format("Status check for copy database task: copy operation is complete for {0}.{1}.", _targetServerName, _targetDatabaseName));
            }
            else
            {
                if (d == DatabaseCopyStatusEnum.SUSPECT || d == DatabaseCopyStatusEnum.NONE)
                {
                    TaskDisposition = TaskDispositionEnum.FAIL;
                    Trace.TraceError(string.Format("Status check for copy database task: copy operation has failed for {0}.{1}.", _targetServerName, _targetDatabaseName));
                }
            }
            return true;
        }

        private bool DoesDatabaseExist(string serverName, string databaseName, string userName, string password, string connectionAlias)
        {
            bool retVal = false;
            string ConnectionString = GetConnectionString(serverName, userName, password, connectionAlias);

            // time the operation
            Stopwatch s = new Stopwatch();
            s.Start();

            try
            {
                // Get an instance of the RetryManager class.
                var retryManager = Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryManager.Instance;

                // Create a retry policy that uses a default retry strategy from the 
                // configuration.
                var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();

                string queryString = string.Format("SELECT count(*) FROM sys.databases WHERE name='{0}'", databaseName);
                using (ReliableSqlConnection conn = new ReliableSqlConnection(ConnectionString, retryPolicy))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = queryString;
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        int count = (int)reader.GetSqlInt32(0);
                        if (count == 1)
                            retVal = true;
                    }
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError(string.Format("Error occured in DoesDatabaseExist({1}.{2}): {0}", ex.Message, serverName, databaseName));
                throw;
            }

            // log the duration of the operation
            s.Stop();
            Trace.TraceInformation(string.Format("DoesDatabaseExist({1}.{2}) elapsed time: {0}ms", s.ElapsedMilliseconds, serverName, databaseName));

            return retVal;
        }

        private bool DoesServerExist(string serverName, string userName, string password, string connectionAlias)
        {
            bool retVal = false;
            string ConnectionString = GetConnectionString(serverName, userName, password, connectionAlias);

            // time the operation
            Stopwatch s = new Stopwatch();
            s.Start();

            try
            {
                // Get an instance of the RetryManager class.
                var retryManager = Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryManager.Instance;

                // Create a retry policy that uses a default retry strategy from the 
                // configuration.
                var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();

                string queryString = string.Format("SELECT count(*) FROM sys.databases");
                using (ReliableSqlConnection conn = new ReliableSqlConnection(ConnectionString, retryPolicy))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = queryString;
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        retVal = true;
                    }
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError(string.Format("Error occured in DoesServerExist({1}): {0}", ex.Message, serverName));
                retVal = false;
            }

            // log the duration of the operation
            s.Stop();
            Trace.TraceInformation(string.Format("DoesServerExist({1}) elapsed time: {0}ms", s.ElapsedMilliseconds, serverName));

            return retVal;
        }

        private void CopyDatabase(string targetServerName, string targetDatabaseName, string connectionAlias, string sourceServerName, string sourceDatabaseName, string userName, string password)
        {

            string ConnectionString = GetConnectionString(targetServerName, userName, password, connectionAlias);

            // time the operation
            Stopwatch s = new Stopwatch();
            s.Start();

            try
            {
                // Get an instance of the RetryManager class.
                var retryManager = Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryManager.Instance;

                // Create a retry policy that uses a default retry strategy from the 
                // configuration.
                var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();

                string queryString = string.Format("CREATE DATABASE {0} AS COPY OF {1}.{2}", targetDatabaseName, sourceServerName, sourceDatabaseName);
                using (ReliableSqlConnection conn = new ReliableSqlConnection(ConnectionString, retryPolicy))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = queryString;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError(string.Format("Error occured in CopyDatabase({2}.{1}): {0}", ex.Message, targetDatabaseName, targetServerName));
                throw;
            }

            // log the duration of the operation
            s.Stop();
            Trace.TraceInformation(string.Format("CopyDatabase({2}.{1}) elapsed time: {0}", s.Elapsed, targetDatabaseName, targetServerName));

            return;
        }

        private void DropDatabase(string serverName, string databaseName, string userName, string password, string connectionAlias)
        {

            string ConnectionString = GetConnectionString(serverName, userName, password, connectionAlias);

            // time the operation
            Stopwatch s = new Stopwatch();
            s.Start();

            try
            {
                // Get an instance of the RetryManager class.
                var retryManager = Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryManager.Instance;

                // Create a retry policy that uses a default retry strategy from the 
                // configuration.
                var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();

                string queryString = string.Format("DROP DATABASE {0}", databaseName);
                using (ReliableSqlConnection conn = new ReliableSqlConnection(ConnectionString, retryPolicy))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = queryString;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError(string.Format("Error occured in DropDatabase({1}.{2}): {0}", ex.Message, serverName, databaseName));
            }

            // log the duration of the operation
            s.Stop();
            Trace.TraceInformation(string.Format("DropDatabase({1}.{2}) elapsed time: {0}", s.Elapsed, serverName, databaseName));

            return;
        }

        private DatabaseCopyStatusEnum DatabaseCopyStatus(string serverName, string databaseName, string userName, string password, string connectionAlias)
        {
            DatabaseCopyStatusEnum retVal = DatabaseCopyStatusEnum.NONE;
            string ConnectionString = GetConnectionString(serverName, userName, password, connectionAlias);

            // time the operation
            Stopwatch s = new Stopwatch();
            s.Start();

            try
            {
                // Get an instance of the RetryManager class.
                var retryManager = Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryManager.Instance;

                // Create a retry policy that uses a default retry strategy from the 
                // configuration.
                var retryPolicy = retryManager.GetDefaultSqlConnectionRetryPolicy();

                string queryString = string.Format("SELECT state_desc FROM sys.databases WHERE name='{0}'", databaseName);
                using (ReliableSqlConnection conn = new ReliableSqlConnection(ConnectionString, retryPolicy))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = queryString;
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        string status = (string)reader.GetSqlString(0);
                        if (status == "ONLINE")
                            retVal = DatabaseCopyStatusEnum.ONLINE;
                        else if (status == "COPYING")
                            retVal = DatabaseCopyStatusEnum.COPYING;
                        else if (status == "SUSPECT")
                            retVal = DatabaseCopyStatusEnum.SUSPECT;
                        else
                            retVal = DatabaseCopyStatusEnum.NONE;
                    }
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError(string.Format("Error occured in DatabaseCopyStatus({1}.{2}): {0}", ex.Message, serverName, databaseName));
                throw;
            }

            // log the duration of the operation
            s.Stop();
            Trace.TraceInformation(string.Format("DatabaseCopyStatus({1}.{2}) elapsed time: {0}ms", s.ElapsedMilliseconds, serverName, databaseName));

            return retVal;
        }

        private string GetConnectionString(string serverName, string userName, string password, string connectionAlias)
        {
            string cs;

            if (string.IsNullOrEmpty(connectionAlias))
            {
                SqlConnectionStringBuilder connString1Builder;
                connString1Builder = new SqlConnectionStringBuilder();
                if (serverName == "localdb")
                    connString1Builder.DataSource = @"(localdb)\v11.0";
                else
                    connString1Builder.DataSource = string.Format("{0}.database.windows.net", serverName);

                connString1Builder.InitialCatalog = "master";
                connString1Builder.Encrypt = true;
                connString1Builder.TrustServerCertificate = false;
                connString1Builder.UserID = userName;
                connString1Builder.Password = password;
                cs = connString1Builder.ConnectionString;
            }
            else
            {
                ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;
                cs = settings[connectionAlias].ConnectionString;
            }

            return cs;
        }
    }
}
